<?php defined('BASEPATH') OR exit('No direct script access allowed');


class Category_model extends WS_model
{

    private function get_count_ofCategorySQL()
    {
        $this->db->select('count(*) AS count,wp.category_seq_id AS cid,max(w.wallpaper_date_created) as last_created, max(w.wallpaper_date_modified) as last_modified');
        $this->db->from('wallpapers_path wp');
        $this->db->join('wallpapers w', 'w.wallpaper_seq_id = wp.wallpaper_seq_id');
        $this->db->where('w.wallpaper_seq_id IS NOT NULL');
        $this->db->where('w.wallpaper_status', '1');
        $this->global_wallpaper();
        $this->db->group_by('wp.category_seq_id');
        return $this->sql();
    }

    public function get_categories($filter = array())
    {
        $show_disabled = (isset($filter['show_disabled']) ? TRUE : FALSE);
        $closure = (isset($filter['closure']) ? TRUE : FALSE);
        $count = (isset($filter['count']) ? TRUE : FALSE);
        $perPage = (isset($filter['per_page']) ? $filter['per_page'] : FALSE);
        $offset = (isset($filter['offset']) ? $filter['offset'] : FALSE);
        $display_only_available_category = (isset($filter['display_only_available_category']) ? $filter['display_only_available_category'] : FALSE);

        if ($closure && !$count) {
            $sql = $this->get_count_ofCategorySQL();
            $this->db->select("wp.*,t2.*,GROUP_CONCAT(c.category_name order by c1.lvl desc SEPARATOR ' > ') AS concat_name,GROUP_CONCAT(c.category_slug order by c1.lvl desc SEPARATOR '/') AS cFullSlug,");
            $this->db->from('closures c1');
            $this->db->join('category c', 'c.category_seq_id = c1.ancestor');
            $this->db->join('category t2', 't2.category_seq_id = c1.descendant');
            $this->db->join('(' . $sql . ') wp', 'wp.cid = t2.category_seq_id', 'left');
            if ($display_only_available_category)
                $this->db->where('wp.count is NOT NULL');
            $this->db->group_by('c1.descendant');
            $this->db->order_by('concat_name', 'asc');
            if (isset($filter['name']) && $filter['name']) {
                $this->db->having('concat_name like "%' . $filter['name'] . '%"');
            }
        } else {
            $this->db->from('category c');
        }

        if (!$show_disabled)
            $this->db->where('c.category_status', 1);

        if (isset($filter['status'])) {
            $this->db->like('c.category_status', $filter['status']);
        }

        $this->db->order_by('c.category_name', 'asc');

        if ($count) {
            if (isset($filter['name']) && $filter['name']) {
                $this->db->like('c.category_name', $filter['name']);
            }
            return $this->db->count_all_results();
        }

        if ($perPage)
            $this->db->limit($perPage, $offset);

        return $this->result();
    }

    public function get_CategoryTree($filter = array())
    {
        return $this->filter_category($this->get_categories($filter));
    }

    public function get_categoryDropDownTree($excludeID = array(), $slug_in_key = false, $filter = array())
    {
        return $this->DropdownTree($this->get_CategoryTree($filter), '', $excludeID, $slug_in_key);
    }

    private function filter_category($data, $parentId = 0)
    {
        $array = array();
        if (!empty($data)) {
            foreach ($data as $element) {
                if ($element->category_parent_id == $parentId) {
                    $children = $this->filter_category($data, $element->category_seq_id);
                    if ($children) {
                        $element->children = $children;
                    }
                    $array [$element->category_slug] = $element;
                }
            }
        }
        return $array;
    }

    private function DropdownTree($data, $pad = '', $exclude = array(), $slug_in_key = false)
    {
        static $options;
        if (is_array($data)) {
            foreach ($data as $d) {
                if (!is_array($exclude))
                    $exclude = array($exclude);
                if (!in_array($d->category_seq_id, $exclude)) {
                    if ($slug_in_key) {
                        $options[$d->category_slug] = ($pad ? $pad . ' ' : '') . $d->category_name;
                    } else {
                        $options[$d->category_seq_id] = ($pad ? $pad . ' ' : '') . $d->category_name;
                    }
                    if (isset($d->children) && !empty($d->children)) {
                        $this->DropdownTree($d->children, $pad . '------', $exclude, $slug_in_key);
                    }
                }
            }
        }
        return $options;
    }

    public function insert($data)
    {
        $this->db->insert('category', $data);
        $insert_id = $this->db->insert_id();
        $this->insert_tree($insert_id, $data['category_parent_id']);
        return $insert_id;
    }

    public function insert_tree($category_id, $parent_id = 0)
    {
        $sql = 'SELECT ancestor, ' . $category_id . ', lvl+1
				FROM {PRE}closures
				WHERE descendant = ' . ($parent_id ? $parent_id : 0) . '
				UNION ALL
				SELECT ' . $category_id . ',' . $category_id . ',0';

        $query = 'INSERT INTO {PRE}closures (ancestor, descendant,lvl) ' . $sql . '';
        $this->db->query($query);
    }

    public function update($data, $id, $path_changed = false)
    {
        $this->db->update('category', $data, array('category_seq_id' => $id));
        if ($path_changed) {
            $query1 = 'DELETE a FROM {PRE}closures AS a ';
            $query1 .= 'JOIN {PRE}closures AS d ON a.descendant = d.descendant ';
            $query1 .= 'LEFT JOIN {PRE}closures AS x ';
            $query1 .= 'ON x.ancestor = d.ancestor AND x.descendant = a.ancestor ';
            $query1 .= 'WHERE d.ancestor = ' . $id . '  AND x.ancestor IS NULL';
            $this->db->query($query1);
            $query2 = 'INSERT INTO {PRE}closures (ancestor, descendant, lvl) ';
            $query2 .= 'SELECT a.ancestor, b.descendant, a.lvl+b.lvl+1 ';
            $query2 .= 'FROM {PRE}closures AS a JOIN {PRE}closures AS b ';
            $query2 .= 'WHERE b.ancestor = ' . $id . ' AND a.descendant = ' . ($data['category_parent_id'] ? $data['category_parent_id'] : 0);
            $this->db->query($query2);

            $this->FIX_wallpaper_path_idsByCatID($id);
        }
    }


    private function FIX_wallpaper_path_idsByCatID($id)
    {
        $this->db->from('wallpapers_path');
        $this->db->where('category_seq_id', $id);
        $query = $this->result_array();
        if ($query) {
            $ids = array_pluck('wallpaper_seq_id', $query);
            foreach ($ids as $wid) {
                $this->removeWallpaperPath_($wid);
                $this->insert_path_single_($wid, $id);
            }
        }
        return false;
    }

    public function delete_categoryByID($id)
    {
        $this->db->delete('category', array('category_seq_id' => $id));
        $operand = 'select descendant as id from {PRE}closures where ancestor = ' . $id;
        $query = 'select id, descendant from {PRE}closures where descendant IN (' . $operand . ')';
        $result_start = $this->db->query($query);

        if ($result_start->num_rows() > 0) {
            $ids = array_pluck('id', $result_start->result());
            $descendants = array_pluck('descendant', $result_start->result());
            $this->db->where_in('id', $ids)->delete('{PRE}closures');
            $this->db->where_in('category_seq_id', $descendants)->delete('{PRE}category');
            $others = $this->get_others_catID();
            if ($others)
                $other_id = $others->category_seq_id;
            else {
                $other_id = $this->createCategoryID();
            }
            $pathIds = array();
            $deleteIDS = array();
            foreach ($descendants as $cid) {
                $wallpapers_ids = array_pluck('wallpaper_seq_id', $this->get_wallpapersOFCID($cid));
                if (!empty($wallpapers_ids)) {
                    foreach ($wallpapers_ids as $id) {
                        $pathIds [$id] = array(
                            'wallpaper_seq_id' => $id,
                            'category_seq_id' => $other_id
                        );
                        $deleteIDS[] = $id;
                    }
                }
            }
            if (!empty($deleteIDS)) {
                $this->db->where_in('wallpaper_seq_id', $deleteIDS);
                $this->db->delete('wallpapers_path');
            }
            if (!empty($pathIds)) {
                $this->db->update_batch('wallpapers', $pathIds, 'wallpaper_seq_id');
                $this->db->insert_batch('wallpapers_path', $pathIds);
            }
        }

    }

    private function createCategoryID()
    {
        $insert_data = array(
            'category_slug' => 'others',
            'category_name' => 'Others',
            'category_status' => 1
        );
        return $this->insert($insert_data);
    }

    private function get_wallpapersOFCID($cid)
    {
        $this->db->select('wallpaper_seq_id');
        $this->db->from('wallpapers');
        $this->db->where('category_seq_id', $cid);
        return $this->result_array();
    }

    private function get_others_catID()
    {
        $this->db->from('category');
        $this->db->where('category_slug', 'others');
        $this->db->where('category_parent_id IS NULL');
        return $this->row();
    }

    public function delete_bulkCategory($cat_ids)
    {
        if (!empty($cat_ids)) {
            $i = 0;
            foreach ($cat_ids as $id) {
                $this->delete_categoryByID($id);
                $i++;
            }
            return $i;
        }
    }

    private function get_categoryBY($filter = array())
    {
        $this->db->from('category c');
        foreach ($filter as $k => $v) {
            $this->db->where($k, $v);
        }
        $this->db->order_by('c.category_name', 'asc');
        return $this->row();
    }

    public function get_categoryBySlug($slug)
    {
        return $this->get_categoryBY(array('c.category_slug' => $slug));
    }

    public function get_categoryByID($id)
    {
        return $this->get_categoryBY(array('c.category_seq_id' => $id));
    }

    public function get_categoryBYCFullSlug($cFull_slug)
    {
        $category_sql = $this->wallCatTemplate();
        $this->db->from('category c');
        $this->db->join("($category_sql) as cf", 'c.category_seq_id = cf.category_seq_id', 'left');
        $this->db->where('LOWER(cFullSlug)', strtolower($cFull_slug));
        $this->db->where('c.category_status', 1);
        return $this->row();
    }

    public function get_parent_categories()
    {
        $views = $this->wallpaperViews();
        $this->db->select('wp.category_seq_id as wpcid,w.*');
        $this->db->from('wallpapers_path wp');
        $this->db->join('wallpapers w', 'w.wallpaper_seq_id = wp.wallpaper_seq_id');
        $this->db->join("($views) as v", 'v.wv_wid = w.wallpaper_seq_id', 'left');
        $this->db->group_by('wp.category_seq_id');
        $this->db->order_by('total_views', 'desc');
        $wallpaper_sql = $this->sql();

        $this->db->select('w.*');
        $this->wallCatTemplate(false);
        $this->db->join("($wallpaper_sql) as w", 'w.wpcid = c.category_seq_id', 'left');
        $this->db->where('t2.category_parent_id IS NULL');
        $this->db->where('w.wallpaper_seq_id IS NOT NULL');
        $this->db->where('t2.category_status', 1);
        $this->db->order_by('t2.category_name','asc');
        return $this->result();
    }

}